﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace insurance.application
{
    /// <summary>
    /// Summary description for ApplyMain
    /// </summary>
    public class ApplyMainDAL
    {
        public ApplyMainDAL()
        {

        }
        

        public void ApplyInsurance(
                   int ApplicationId,
                    String CustId,
                    String ApplicationSatus,
                    String AccountNo,
                    DateTime? ApplicationDate,
                    DateTime? StartDate,
                    DateTime? EndDate,
                    String PaymentWay,
                    DateTime? ApprovalDate,
                    String StaffId,
                    String SavingAccountNo,
                    String ApplyCategory,
                    String ApplyProduct,
                    String ProductType,
                    LinkedList<ApplyInsuredDAL> Insureds)
        {

            SqlConnection conn = DBManager.getSqlConnection();
            conn.Open();
           
            try
            {
                // Start a local transaction.
                string sql = "INSERT APPLICATION_INSURANCE (custID, applicationStatus, accountNo, applicationDate, paymentPeriod, approvalDate, staffID, savingAccountNo, applyCategory, applyProduct, productType)" +
                             " VALUES ( " +
                             "@custID, " +
                             "@applicationStatus, " +
                             "@accountNo, " +
                             "@applicationDate, " +
                             "@paymentPeriod, " +
                             "@approvalDate, " +
                             "@staffID, " +
                             "@savingAccountNo, " +
                             "@applyCategory, " +
                             "@applyProduct, " +
                             "@productType);SELECT SCOPE_IDENTITY()";//get the applicationId


                var cmd = new SqlCommand(sql, conn);
                var param = new SqlParameter[11];
                 param[0] = new SqlParameter("custID", SqlDbType.VarChar, 50);
                 param[1] = new SqlParameter("applicationStatus", SqlDbType.VarChar, 50);
                 param[2] = new SqlParameter("accountNo", SqlDbType.VarChar, 50);
                 param[3] = new SqlParameter("applicationDate", SqlDbType.Date);
                 param[4] = new SqlParameter("paymentPeriod", SqlDbType.VarChar, 50);
                 param[5] = new SqlParameter("approvalDate", SqlDbType.Date);
                 param[6] = new SqlParameter("staffID", SqlDbType.VarChar, 50);
                 param[7] = new SqlParameter("savingAccountNo", SqlDbType.VarChar, 50);
                 param[8] = new SqlParameter("applyCategory", SqlDbType.VarChar, 50);
                 param[9] = new SqlParameter("applyProduct", SqlDbType.VarChar, 50);
                 param[10] = new SqlParameter("productType", SqlDbType.VarChar, 50);


                 param[0].Value = (object)CustId ?? DBNull.Value;
                 param[1].Value = (object)ApplicationSatus ?? DBNull.Value;
                 param[2].Value = (object)AccountNo ?? DBNull.Value;
                 param[3].Value = (object)ApplicationDate ?? DBNull.Value;
                 param[4].Value = (object)PaymentWay ?? DBNull.Value;
                 param[5].Value = (object)ApprovalDate ?? DBNull.Value;
                 param[6].Value = (object)StaffId ?? DBNull.Value;
                 param[7].Value = (object)SavingAccountNo ?? DBNull.Value;
                 param[8].Value = (object)ApplyCategory ?? DBNull.Value;
                 param[9].Value = (object)ApplyProduct ?? DBNull.Value;
                 param[10].Value = (object)ProductType ?? DBNull.Value;
                

                 foreach (var t in param)
                 {
                     cmd.Parameters.Add(t);
                 }
                
                 cmd.CommandType = CommandType.Text;
                 
                //cmd.ExecuteNonQuery();
                object newId = cmd.ExecuteScalar();
                int newIdInt = int.Parse(((Decimal)newId).ToString());


                foreach (ApplyInsuredDAL insured in Insureds)
                {
                    insured.ApplicationId = newIdInt;
                    insured.save(conn);
                }


            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(@"Commit Exception Type: {0}", ex.GetType());
                System.Diagnostics.Debug.WriteLine(@"Message: {0}", ex.Message);
                throw ex;
            }

            conn.Close();
        }



    }
}